﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using DTO;

namespace DAO
{
    public class SearchDAO : AbstractDAO
    {
         //lay ma de select du lieu
        public static List<MaDTO> TimKiem(string input)
        {
            OleDbConnection ketNoi = null;
            List<MaDTO> ds = new List<MaDTO>();

            try
            {

                ketNoi = KetNoi();
                string chuoiLenh = null;
                for (int i = 0; i < 6; i++ )
                {
                    switch (i)
                    { 
                        case 0:
                            chuoiLenh = @"select MaDichVu from TUKHOADICHVU where @input like '%'+TuKhoaDichVu+'%'";
                            break;
                        case 1:
                            chuoiLenh = @"select MaDuong from TUKHOADUONG where @input like '%'+TuKhoaDuong+'%'";
                            break;
                        case 2:
                            chuoiLenh = @"select MaTenDiaDiem from TUKHOADIADIEM where @input like '%'+TuKhoaTenDiaDiem+'%'";
                            break;
                        case 3:
                            chuoiLenh = @"select MaPhuong from TUKHOAPHUONG where @input like '%'+TuKhoaPhuong+'%'";
                            break;
                        case 4:
                            chuoiLenh = @"select MaQuanHuyen from TUKHOAQUANHUYEN where @input like '%'+TuKhoaQuanHuyen+'%'";
                            break;
                        case 5:
                            chuoiLenh = @"select MaTinhThanh from TUKHOATINHTHANH where @input like '%'+TuKhoaTinhThanh+'%'";
                            break;
                    }
                    
                    OleDbCommand Lenh = new OleDbCommand(chuoiLenh, ketNoi);
                    OleDbParameter thamSo = new OleDbParameter("@input", OleDbType.VarChar);
                    thamSo.Value = input;
                    Lenh.Parameters.Add(thamSo);

                    OleDbDataReader boDoc = Lenh.ExecuteReader();

                    while (boDoc.Read())
                    {
                        MaDTO ma = new MaDTO();
                        switch (i)
                        {
                            case 0:
                                if (!boDoc.IsDBNull(0))
                                    ma.MaDichVu = boDoc.GetInt32(0);
                                break;
                            case 1:
                                if (!boDoc.IsDBNull(0))
                                {
                                    ma.Add("MaDuong", boDoc.GetInt32(0));
                                }
                                break;
                            case 2:
                                if (!boDoc.IsDBNull(0))
                                {
                                    ma.Add("MaDiaDiem", boDoc.GetInt32(0));
                                }
                                break;
                            case 3:
                                if (!boDoc.IsDBNull(0))
                                {
                                    ma.Add("MaPhuong", boDoc.GetInt32(0));
                                }
                                break;
                            case 4:
                                if (!boDoc.IsDBNull(0))
                                {
                                    ma.Add("MaQuan", boDoc.GetInt32(0));
                                }
                                break;
                            case 5:
                                if (!boDoc.IsDBNull(0))
                                {
                                    ma.Add("MaTT", boDoc.GetInt32(0));
                                }
                                break;
                        }
                        

                        ds.Add(ma);
                    }
                }
                
            }
            catch (System.Exception e)
            {
                ds = new List<MaDTO>();
                MessageBox.Show(e.Message); 
            }
            return ds;
        }

        public static List<DuLieuDTO> TraCuuDuLieu(List<MaDTO> ds)
        {
            OleDbConnection ketNoi = null;
            List<DuLieuDTO> kq = null;

            try
            {
                kq = new List<DuLieuDTO>();
                ketNoi = KetNoi();
                string chuoiLenh;
                chuoiLenh = "select dv.TenDichVu,tdd.TenDiaDiem,dl.SoNha,dg.TenDuong,ph.TenPhuong,qh.TenQuanHuyen,tt.TenTinhThanh,dl.KinhDo,dl.ViDo,dl.ChuThich from DICHVU dv,DULIEU dl,TENDIADIEM tdd,DUONG dg,PHUONG ph,QUANHUYEN qh,TINHTHANH tt where dl.MaDichVu=dv.MaDichVu and dl.MaTenDiaDiem=tdd.MaTenDiaDiem and dl.MaDuong=dg.MaDuong and dl.MaPhuong=ph.MaPhuong and dl.MaQuanHuyen=qh.MaQuanHuyen and dl.MaTinhThanh=tt.MaTinhThanh";

                //for (int i = 0; i < ds.Count; i++) //Phát sinh query để truy vấn
                //{
                //    if (ds[i].MaDichVu!=null)
                chuoiLenh += " and dl.MaDichVu=";
                chuoiLenh += ds[0].MaDichVu.ToString();
                //}

                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbDataReader boDoc = lenh.ExecuteReader();

                while (boDoc.Read())
                {
                    DuLieuDTO duLieu = new DuLieuDTO();

                    if (!boDoc.IsDBNull(0))
                        duLieu.DichVu = boDoc.GetString(0);
                    if (!boDoc.IsDBNull(1))
                        duLieu.TenDiaDiem = boDoc.GetString(1);
                    if (!boDoc.IsDBNull(2))
                        duLieu.SoNha = boDoc.GetString(2);
                    if (!boDoc.IsDBNull(3))
                        duLieu.Duong = boDoc.GetString(3);
                    if (!boDoc.IsDBNull(4))
                        duLieu.Phuong = boDoc.GetString(4);
                    if (!boDoc.IsDBNull(5))
                        duLieu.QuanHuyen = boDoc.GetString(5);
                    if (!boDoc.IsDBNull(6))
                        duLieu.TinhThanh = boDoc.GetString(6);
                    if (!boDoc.IsDBNull(7))
                        duLieu.KinhDo = boDoc.GetDouble(7).ToString();
                    if (!boDoc.IsDBNull(8))
                        duLieu.ViDo = boDoc.GetDouble(8).ToString();
                    if (!boDoc.IsDBNull(9))
                        duLieu.ChuThich = boDoc.GetString(9);

                    kq.Add(duLieu);

                }
            }
            catch (Exception ex)
            {
                kq = new List<DuLieuDTO>();
                MessageBox.Show(ex.Message);
            }
            finally
            {
                ketNoi.Close();
            }

            return kq;
        }
    }
}
